package com.stgl.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtils {

	public static String EXCEL2003 = "EXCEL2003";
	public static String EXCEL2007 = "EXCEL2007";
	public static String EXCEL2010 = "EXCEL2010";



	/**
	 * 根据创建好的workbook对象，向响应端输出EXCEL
	 * 
	 * @param workbook
	 * @param exportFileName
	 * @param response
	 * @throws Exception
	 */
	public static void exportExcelData(Workbook workbook,
			String exportFileName, HttpServletResponse response)
			throws Exception {
		response.reset();
		StringBuffer headerSB = new StringBuffer();
		headerSB.append("attachment; filename=");
		headerSB.append(exportFileName);
		response.setHeader("Content-Disposition", new String(headerSB
				.toString().getBytes("GBK"), "ISO-8859-1"));
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Transfer-Encoding", "binary");
		response.setHeader("Cache-Control",
				"must-revalidate, post-check=0, pre-check=0");
		response.setHeader("Pragma", "public");
		// 获得输出流
		OutputStream out = null;
		// 输出
		try {
			out = response.getOutputStream();
			workbook.write(out);
			out.flush();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				out.close();
			}
		}
	}


	public static String getHSSFCellValue(HSSFCell cell) {
		if(cell!=null){
			switch (cell.getCellType()) {
				case HSSFCell.CELL_TYPE_NUMERIC:
					//BigDecimal db = new BigDecimal(cell.getNumericCellValue()).setScale(4,BigDecimal.ROUND_HALF_DOWN);
					SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					Date date=cell.getDateCellValue();
					return sdf.format(date);
					/*java.text.DecimalFormat  df=new   java.text.DecimalFormat("#.####");
					return df.format(cell.getNumericCellValue());*/
				case HSSFCell.CELL_TYPE_STRING:
					return String.valueOf(cell.getStringCellValue()).replaceAll("\\s", "");
				case HSSFCell.CELL_TYPE_BOOLEAN:
					return String.valueOf(cell.getBooleanCellValue()).replaceAll("\\s", "");
				case HSSFCell.CELL_TYPE_FORMULA:
					return String.valueOf(cell.getCellFormula()).replaceAll("\\s", "");
				default:
					break;
			}
		}
		return "";
	}


	public static String getXSSFCellValue(XSSFCell cell) {
		if(cell!=null){
			switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_NUMERIC:
					java.text.DecimalFormat  df=new   java.text.DecimalFormat("#.####");
					return df.format(cell.getNumericCellValue());
				case XSSFCell.CELL_TYPE_STRING:
					return String.valueOf(cell.getStringCellValue()).replaceAll("\\s", "");
				case XSSFCell.CELL_TYPE_BOOLEAN:
					return String.valueOf(cell.getBooleanCellValue()).replaceAll("\\s", "");
				case XSSFCell.CELL_TYPE_FORMULA:
					return String.valueOf(cell.getCellFormula()).replaceAll("\\s", "");
				default:
					break;
			}
			return "";
		}else{
			return "";
		}
	}


	public static void main(String args[])throws Exception{
		File f = new File("C:\\Users\\Sonic\\AppData\\Local\\Temp\\tomca-docbase.7339093254572283926.8080\\upload\\20190516162808.xls");
		InputStream in = new FileInputStream(f);
		byte b[]=new byte[(int)f.length()];     //创建合适文件大小的数组
		in.read(b);    //读取文件中的内容到b[]数组
		in.close();
		System.out.println(new String(b));
	}

}
